package com.haifeng.demo;

import com.haifeng.pojo.Emp;
import com.haifeng.utils.JDBCUtils;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class HelloController {

    @FXML
    private TextField ename;
    @FXML
    private TextField job;
    @FXML
    private Button select_btn;
    @FXML
    TableView<Emp> table;

    @FXML
    protected void onSelectButtonClick() throws SQLException {

        List<Emp> empList=new ArrayList<>();
        ObservableList list = FXCollections.observableList(empList);

        TableColumn tc1 = new TableColumn("员号");
        tc1.setCellValueFactory(new PropertyValueFactory<>("empno"));
        tc1.setMinWidth(100);
        TableColumn tc2 = new TableColumn("员工名");
        tc2.setCellValueFactory(new PropertyValueFactory<>("ename"));
        tc2.setMinWidth(100);
        TableColumn tc3 = new TableColumn("工作");
        tc3.setMinWidth(100);
        tc3.setCellValueFactory(new PropertyValueFactory<>("job"));
        TableColumn tc4 = new TableColumn("部门号");
        tc4.setMinWidth(100);
        tc4.setCellValueFactory(new PropertyValueFactory<>("deptno"));
        table.getColumns().clear();
        table.getColumns().addAll(tc1, tc2, tc3, tc4);


        Connection connection = JDBCUtils.getConnection();
        String param0 = ename.getText();
        String param1 = job.getText();
        StringBuilder sb = new StringBuilder();
        sb.append("  Select empno, ename,  ");
        sb.append("  deptno, job from emp       ");
        sb.append("  where 1 = 1           ");
        if (null != param0 && !"".equals(param0)) {
            sb.append("  and ename = ?           ");
        }
        if (null != param1 && !"".equals(param1)) {
            sb.append("  and job = ?           ");
        }

        // 预处理执行器 继承自Statement
        PreparedStatement st = connection.prepareStatement(sb.toString());
        int index = 0;
        if (null != param0 && !"".equals(param0)) {

            st.setString(++index, param0);
        }
        if (null != param1 && !"".equals(param1)) {
            st.setString(++index, param1);
        }
        System.out.println(sb.toString());


        ResultSet resultSet = st.executeQuery();

        while (resultSet.next()){

            Emp item = new Emp();
            list.add(item);
            item.setEmpno(resultSet.getInt("empno"));
            item.setEname(resultSet.getString("ename"));
            item.setDeptno(resultSet.getInt("deptno"));
            item.setJob(resultSet.getString("job"));




            System.out.println(item);
        }
        table.setItems(list);
//        table.setItems(list);
    }
}